package database

import (
	"database/sql"
	"fmt"
	"log"
	"strings"
	"time"

	"goEmqxToDb/config"

	"github.com/go-sql-driver/mysql"
	_ "github.com/go-sql-driver/mysql"
)

// MySQLClient MySQL客户端
type MySQLClient struct {
	db *sql.DB
}

// NewMySQLClient 创建新的MySQL客户端
func NewMySQLClient(task config.Task) (*MySQLClient, error) {
	// 添加调试信息
	log.Printf("尝试连接MySQL数据库:")
	log.Printf("  主机: %s", task.Host)
	log.Printf("  端口: %d", task.Port)
	log.Printf("  数据库: %s", task.Database)
	log.Printf("  用户名: %s", task.Username)
	// log.Printf("  密码: %s", strings.Repeat("*", len(task.Password)))
	log.Printf("  密码: %s 测试", task.Password)

	dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=utf8&parseTime=True&loc=Local",
		task.Username, task.Password, task.Host, task.Port, task.Database)

	log.Printf("dsn: %s", dsn)

	db, err := sql.Open("mysql", dsn)
	if err != nil {
		return nil, fmt.Errorf("连接MySQL失败: %v", err)
	}

	// 测试连接
	if err := db.Ping(); err != nil {
		if mysqlErr, ok := err.(*mysql.MySQLError); ok {
			log.Printf("MySQL错误代码: %d", mysqlErr.Number)
			log.Printf("MySQL错误信息: %s", mysqlErr.Message)
		}
		db.Close()
		return nil, fmt.Errorf("MySQL连接测试失败: %v", err)
	}

	log.Printf("MySQL连接成功: %s:%d/%s", task.Host, task.Port, task.Database)

	return &MySQLClient{db: db}, nil
}

// Close 关闭数据库连接
func (c *MySQLClient) Close() error {
	return c.db.Close()
}

// CreateTableIfNotExists 如果表不存在则创建
func (c *MySQLClient) CreateTableIfNotExists(tableName string, data map[string]interface{}) error {
	// 检查表是否存在
	var exists int
	query := "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = ?"
	err := c.db.QueryRow(query, tableName).Scan(&exists)
	if err != nil {
		return fmt.Errorf("检查表是否存在失败: %v", err)
	}

	if exists > 0 {
		log.Printf("表 %s 已存在", tableName)
		return nil
	}

	// 创建表
	// 添加自增主键
	primaryKeyName := fmt.Sprintf("%s_id", tableName)
	columns := []string{fmt.Sprintf("`%s` BIGINT AUTO_INCREMENT PRIMARY KEY", primaryKeyName)}

	// 添加时间戳字段
	columns = append(columns, "_dbtime TIMESTAMP DEFAULT CURRENT_TIMESTAMP")

	// 根据JSON数据的key生成字段
	for key, value := range data {
		columnType := getMySQLType(value)
		columnName := sanitizeColumnName(key)
		columns = append(columns, fmt.Sprintf("`%s` %s", columnName, columnType))
	}

	createSQL := fmt.Sprintf("CREATE TABLE `%s` (%s)", tableName, strings.Join(columns, ", "))

	_, err = c.db.Exec(createSQL)
	if err != nil {
		return fmt.Errorf("创建表 %s 失败: %v", tableName, err)
	}

	log.Printf("成功创建表: %s", tableName)
	return nil
}

// AddColumnIfNotExists 如果字段不存在则添加
func (c *MySQLClient) AddColumnIfNotExists(tableName string, columnName string, value interface{}) error {
	// 检查字段是否存在
	var exists int
	query := "SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = ? AND column_name = ?"
	err := c.db.QueryRow(query, tableName, columnName).Scan(&exists)
	if err != nil {
		return fmt.Errorf("检查字段是否存在失败: %v", err)
	}

	if exists > 0 {
		return nil
	}

	// 添加字段
	columnType := getMySQLType(value)
	addSQL := fmt.Sprintf("ALTER TABLE `%s` ADD COLUMN `%s` %s", tableName, columnName, columnType)

	_, err = c.db.Exec(addSQL)
	if err != nil {
		return fmt.Errorf("添加字段 %s 失败: %v", columnName, err)
	}

	log.Printf("成功添加字段: %s.%s", tableName, columnName)
	return nil
}

// InsertData 插入数据
func (c *MySQLClient) InsertData(tableName string, data map[string]interface{}) error {
	// 确保表存在
	err := c.CreateTableIfNotExists(tableName, data)
	if err != nil {
		return err
	}

	// 检查并添加缺失的字段
	for key, value := range data {
		columnName := sanitizeColumnName(key)
		err := c.AddColumnIfNotExists(tableName, columnName, value)
		if err != nil {
			return err
		}
	}

	// 构建插入SQL
	// 主键是自增的，不需要手动指定
	columns := []string{"_dbtime"}
	placeholders := []string{"?"}
	values := []interface{}{time.Now()}

	for key, value := range data {
		columnName := sanitizeColumnName(key)
		columns = append(columns, fmt.Sprintf("`%s`", columnName))
		placeholders = append(placeholders, "?")
		values = append(values, value)
	}

	insertSQL := fmt.Sprintf("INSERT INTO `%s` (%s) VALUES (%s)",
		tableName, strings.Join(columns, ", "), strings.Join(placeholders, ", "))

	_, err = c.db.Exec(insertSQL, values...)
	if err != nil {
		return fmt.Errorf("插入数据失败: %v", err)
	}

	log.Printf("成功插入数据到表: %s", tableName)
	return nil
}

// getMySQLType 根据Go类型获取MySQL类型
func getMySQLType(value interface{}) string {
	switch v := value.(type) {
	case string:
		if len(v) > 255 {
			return "TEXT"
		}
		return "VARCHAR(255)"
	case int, int32, int64:
		return "BIGINT"
	case float32, float64:
		return "DOUBLE"
	case bool:
		return "BOOLEAN"
	case []byte:
		return "BLOB"
	default:
		// 对于复杂类型，转换为JSON字符串
		return "TEXT"
	}
}

// sanitizeColumnName 清理列名，移除特殊字符
func sanitizeColumnName(name string) string {
	// 移除或替换不安全的字符
	name = strings.ReplaceAll(name, " ", "_")
	name = strings.ReplaceAll(name, "-", "_")
	name = strings.ReplaceAll(name, ".", "_")

	// 确保列名不以数字开头
	if len(name) > 0 && name[0] >= '0' && name[0] <= '9' {
		name = "col_" + name
	}

	return name
}
